/* ============================================================================= */
/*          TABLE 6 - ACCEPTED VS REJECTED BORROWERS                             */
/* ============================================================================= */

clear all

capture log close
log using "$logs\table_7_rejected.log", replace

//----------------------------------------------------------------------------//
//                 STEP 1: IMPORT & PREPARE BASE DATASETS                      //
//----------------------------------------------------------------------------//

/* Import SIREN and weights from PSM */
use "$output\rejected_psm.dta", clear
    keep siren mdate weight  // Keep only firm identifier, date, and weight
    sort siren mdate
    quietly by siren mdate: gen dup = cond(_N == 1, 0, _n)  // Identify duplicate SIRENs
    drop if dup > 1  // Keep only unique SIRENs
    save "$output\rejected_psm_weight.dta", replace  // Save cleaned weight file

/* Import SIREN - CIB dataset */
use "$output\scr_fiben_reject.dta", clear
    keep if mdate < mofd(date("20200101","YMD"))  // Keep observations before 2020
    keep if mdate > mofd(date("20131231","YMD"))  // Keep observations after 2013
    keep if inrange(deltamonths, -12, 24)  // Keep observations within -12 to +24 months

/* Merge with department dataset */
merge m:1 cib guichet using "$output\CIB_guichet_departement.dta"
keep if _merge == 3  // Keep only matched records
drop _merge

/* Define lender characteristics */
gen newbankrelation = recentbankmedian  // Indicator for new banking relationship
gen near = (dept == departement)  // Indicator for lender being in the same department

/* Merge with matched sample weights */
merge m:1 siren mdate using "$output\rejected_psm_weight.dta"
keep if _merge == 3  // Keep only matched firms
drop _merge

keep if inrange(deltamonths, -12, 24)  // Ensure only the relevant time window is kept

//----------------------------------------------------------------------------//
//                 STEP 2: NEAR VS. DISTANT LENDERS ANALYSIS                   //
//----------------------------------------------------------------------------//

preserve
    gen loan = available + drawn  // Total loan amount

    /* Split loan amount by lender proximity */
    gen loannear = loan * (near == 1)  // Loan from local lenders
    gen loanfar = loan * (near == 0)  // Loan from distant lenders

    /* Aggregate loan amounts */
    collapse (sum) loannear loanfar loan, by(siren accepted post mdate deltamonths year rating weight)

    /* Log transformation */
    gen lloannear = log(loannear)
    gen lloanfar = log(loanfar)

    /* Run regressions for loans from near and distant lenders */
    eststo regnear: reghdfe lloannear i1.accepted##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren)  
	estadd local yearfirm "Y", replace   
    estadd local mdateFE "Y", replace   
    eststo regfar: reghdfe lloanfar i1.accepted##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren)
	estadd local yearfirm "Y", replace   
    estadd local mdateFE "Y", replace   

restore

//----------------------------------------------------------------------------//
//           STEP 3: NEW VS. EXISTING LENDERS & LOAN VOLUME ANALYSIS           //
//----------------------------------------------------------------------------//

preserve
    sort siren mdate cib
    gen leasing = bm + bi

    /* Split loans by lender type (new vs. existing) */
    foreach var in leasing available drawn ltloan stloan co bm bi {
        gen `var'_new = `var' * (newbankrelation == 1)
        gen `var'_old = `var' * (newbankrelation == 0)
    }

    /* Aggregate loan amounts */
    collapse (sum) stloan co co_new co_old ltloan bm bi oc available drawn leasing leasing_old leasing_new bm_new bm_old bi_new bi_old ///
        available_new drawn_new available_old drawn_old ltloan_old ltloan_new stloan_old stloan_new, ///
        by(siren accepted post mdate deltamonths year rating weight)

    /* Log transformation */
    gen lavailable = log(1 + oc)
    gen ldrawn = log(1 + stloan + ltloan + leasing)
    gen lloan = log(1 + stloan + ltloan + bm + bi + oc)
    gen lloannew = log(1 + stloan_new + ltloan_new + bm_new + bi_new + available_new)
    gen lloanold = log(1 + stloan_old + ltloan_old + bm_old + bi_old + available_old)

    /* Run regressions for new and existing lenders */
    eststo regnew: reghdfe lloannew i1.accepted##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren)
	estadd local yearfirm "Y", replace   
    estadd local mdateFE "Y", replace
	
    eststo regexisting: reghdfe lloanold i1.accepted##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren)
    estadd local yearfirm "Y", replace   
    estadd local mdateFE "Y", replace
	
restore

//----------------------------------------------------------------------------//
//                 STEP 4: RATED VS. UNRATED FIRMS ANALYSIS                    //
//----------------------------------------------------------------------------//

/* Load dataset of rated firms */
use "$output\rejected_psm_rated.dta", clear
    keep if mdate < mofd(date("20200101","YMD"))
    keep if mdate > mofd(date("20131231","YMD"))
    keep if inrange(deltamonths, -12, 24)
    gen loan = stloan + ltloan + bm + bi + oc
    gen lloan = log(loan)

    /* Run regression for rated firms */
    eststo regrated: reghdfe lloan i1.accepted##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren)
    estadd local yearfirm "Y", replace 
    estadd local mdateFE "Y", replace  

/* Load dataset of unrated firms */
use "$output\rejected_psm_unrated.dta", clear
    keep if mdate < mofd(date("20200101","YMD"))
    keep if mdate > mofd(date("20131231","YMD"))
    keep if inrange(deltamonths, -12, 24)
    gen loan = stloan + ltloan + bm + bi + oc
    gen lloan = log(loan)

    /* Run regression for unrated firms */
    eststo regunrated: reghdfe lloan i1.accepted##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren)
    estadd local yearfirm "Y", replace 
    estadd local mdateFE "Y", replace  

//----------------------------------------------------------------------------//
//                 STEP 5: EXPORT Table 7b TO LaTeX                             //
//----------------------------------------------------------------------------//

/* Export regression results */
esttab regexisting regnew regnear regfar regrated regunrated using "$tables\Table_7_acc_rej.tex", replace ///
    noomitted nobase fragment gap booktabs label nonote noobs nolines /// 
    b(%9.3f) drop(_cons) se(%9.3f) star(* 0.10 ** 0.05 *** 0.01) ///
    mtitles("Existing Lenders" "New Lenders" "Local Lenders" "Distant Lenders" "Rated" "Unrated") ///
    order(1.accepted#1.post 1.post) varlabel(1.post "Post" 1.accepted#1.post `"Accepted $\times$ Post"') ///
    stats(yearfirm mdateFE N r2, label(`"Firm-Year FE"' `"Month FE"' `"\midrule N"' `"R-sq"') layout("\multicolumn{1}c{@}" "\multicolumn{1}c{@}") fmt(%9.0fc %9.0fc %9.0fc %9.2f))

log close
